package com.huitone.gddw.common.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

/**
 * 创建Excel
 * @author wuluodan
 *
 */
@SuppressWarnings("all")
public class CreateExcel {

	private HSSFWorkbook wb = null;
	private Sheet sheet = null;

	public final static String DATA_START = "data";
	/* 初始化列数 */
	private int initColIndex;
	/* 初始化行数 */
	private int initRowIndex;
	/* 当前列数 */
	private int curColIndex;
	/* 当前行数 */
	private int curRowIndex;
	/* 最后一行的数据 */
	private int lastRowIndex;

	private Row curRow;

	private CellStyle style;
	private CellStyle style2;

	private CreateExcel() {
	}

	public static CreateExcel getInstance() {
		return new CreateExcel();
	}

	public CreateExcel initDataNoTemplate() {
		// 没有模板时初始化创建
		wb = new HSSFWorkbook();
		sheet = this.wb.createSheet("sheet1");
		initRowIndex = 0;
		initColIndex = 0;
		initStyle();
		return this;
	}

	public CreateExcel initStyle() {
		// 初始化样式
		style = wb.createCellStyle();
		style2 = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER); // 居中
		style2.setAlignment(CellStyle.ALIGN_CENTER);
		
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		
		return this;
	}

	/**
	 * 初始化当前的行列
	 * 
	 * @return
	 */
	private CreateExcel initConfigDataNoTemplate() {
		curRowIndex = initRowIndex;
		curColIndex = initColIndex;
		return this;
	}

	/**
	 * 创建新的行
	 * 
	 * @return
	 */
	private CreateExcel createRow() {
		curColIndex = initColIndex;
		curRow = this.sheet.createRow(curRowIndex);
		curRowIndex++;
		return this;
	}

	/**
	 * 创建表头
	 * 
	 * @param title
	 *            表头内容
	 * @param colSpan
	 *            表头宽度(占据的单元格的列的数量)
	 * @return
	 */
	public CreateExcel createTitle(String title, int colSpan) {
		createRow();
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, colSpan));
		// 头部占据2行所以要继续加一行
		curRowIndex++;
		Cell c = curRow.createCell(curColIndex);
		c.setCellValue(title);
		// 重置样式防止出现样式不对
		CellStyle style = wb.createCellStyle();
		
		// 居中
		style.setAlignment(CellStyle.ALIGN_CENTER);
		Font f = wb.createFont();
		// 设置加粗
		f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 字体
		f.setFontHeightInPoints((short) 14);
		style.setFont(f);
		
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		c.setCellStyle(style);
		return this;
	}

	/**
	 * 新建表头
	 * 
	 * @param head
	 *            包含表头字符串的集合
	 * @return
	 */
	public CreateExcel createHead(List<String> head) {
		createRow();
		Cell c = null;
		// 设置表头样式
		CellStyle style = wb.createCellStyle();
		Font f = wb.createFont();
		// 设置加粗
		f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(f);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		for (String query : head) {
			c = curRow.createCell(curColIndex);
			curColIndex++;
			this.setCellValue(String.class, query, c);
			c.setCellStyle(style);
		}
		return this;
	}
	
	
	/**
	 * 新建表头
	 * 
	 * @param head
	 *            包含表头字符串的集合
	 * @return
	 */
	public CreateExcel createHeadProperty(List<ColProperty> head) {
		createRow();
		Cell c = null;
		// 设置表头样式
		CellStyle style = wb.createCellStyle();
		Font f = wb.createFont();
		// 设置加粗
		f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(f);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		for (ColProperty colp : head) {
			c = curRow.createCell(curColIndex);
			curColIndex++;
			this.setCellValue(String.class, colp.getTitle(), c);
			if(StringUtils.isNotBlank(colp.getWidth())){
				this.sheet.setColumnWidth((colp.getIndex()-1),Integer.valueOf(colp.getWidth())*50);
			}
			c.setCellStyle(style);
		}
		return this;
	}

	/**
	 * 表的内容
	 * 
	 * @param body
	 *            包含行数据的集合
	 * @return
	 */
	public CreateExcel createBody(List<?> body) {
		for (Object t : body) {
			createRow();
			Map<String, ColProperty> indexMap = findBeanProperty(t.getClass());
			Method m = null;
			try {
				for (int i = 0; i < indexMap.size(); i++) {
					Iterator<String> it = indexMap.keySet().iterator();
					while (it.hasNext()) {
						String key = it.next();
						ColProperty value = indexMap.get(key);
						// 从1开始查找字段
						if (value.getIndex() != (i + 1)) {
							continue;
						}
						// 得到get方法
						m = t.getClass().getMethod("get" + toUpperCaseFirstOne(key), null);
						Class<?> clazz = value.getType();
						// 反射得到值
						Object oo = m.invoke(t, null);

						setCellValue(clazz, oo, createCell());
					}
				}
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			}

		}
		return this;
	}
	
	public CreateExcel createBody(List<?> body, List<Class> listClazz) {
		for (Object t : body) {
			createRow();
			Map<String, ColProperty> indexMap = findBeanProperty(t.getClass(), listClazz);
			Method m = null;
			try {
				for (int i = 0; i < indexMap.size(); i++) {
					Iterator<String> it = indexMap.keySet().iterator();
					while (it.hasNext()) {
						String key = it.next();
						ColProperty value = indexMap.get(key);
						// 从1开始查找字段
						if (value.getIndex() != (i + 1)) {
							continue;
						}
						// 得到get方法
						m = t.getClass().getMethod("get" + toUpperCaseFirstOne(key), null);
						Class<?> clazz = value.getType();
						// 反射得到值
						Object oo = m.invoke(t, null);

						setCellValue(clazz, oo, createCell());
					}
				}
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			}

		}
		return this;
	}

	public CreateExcel createBodyCoalition(List<?> body) {

		if (null == body || body.size() == 0) {
			throw new RuntimeException("表内容不能为空!");
		}

		for (Object t : body) {
			curColIndex = 0;
			createRow();
			Method m = null;
			try {
				m = t.getClass().getMethod("getL", null);
				Object oo = m.invoke(t, null);
				List<Object> ol = (List) oo;

				Field fs[] = t.getClass().getDeclaredFields();
				for (Field f : fs) {
					String methodName = f.getName().substring(0, 1).toUpperCase() + f.getName().substring(1);
					if (f.getType() != java.util.List.class) {
						m = t.getClass().getDeclaredMethod("get" + methodName);
						Object oper = m.invoke(t, null);
//						System.out.println(oper);
						setCellValue(f.getType(), oper, createCell(ol.size() - 1, 1));
					}
				}
				for (int i = 0; i < ol.size(); i++) {
					if (i != 0) {
						createRow();
						curColIndex = 3;
					}

					Field fields[] = ol.get(i).getClass().getDeclaredFields();
					for (Field field : fields) {
						String methodNameL = field.getName().substring(0, 1).toUpperCase()
								+ field.getName().substring(1);
						m = ol.get(i).getClass().getDeclaredMethod("get" + methodNameL);
						Object value = m.invoke(ol.get(i), null);
						setCellValue(value.getClass(), value, createCell());
					}
//					System.out.println("");
				}
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			}
		}
		return this;
	}

	public CreateExcel createBodyCoalition(Map<String, Map<String, List<List<Object>>>> result) {

		if (result == null || result.size() == 0)
			throw new RuntimeException("表内容不能为空!");
		Iterator<String> resultIt = result.keySet().iterator();
		while (resultIt.hasNext()) {
			curColIndex = 0;
			createRow();
			String resultKey = resultIt.next();
			Map<String, List<List<Object>>> map = result.get(resultKey);
			setCellValue(String.class, resultKey, createCell(countMapList(map) - 1, 1));
			boolean isSecondColFirst = true;

			Iterator<String> mapIt = map.keySet().iterator();
			while (mapIt.hasNext()) {
				if (!isSecondColFirst) {
					createRow();
					curColIndex = 1;
				}
				isSecondColFirst = false;
				String mapKey = mapIt.next();
				List<List<Object>> l = map.get(mapKey);
				setCellValue(String.class, mapKey, createCell(l.size() - 1, 1));

				for (int i = 0; i < l.size(); i++) {
					if (i != 0) {
						createRow();
						curColIndex = 2;
					}
					for (int j = 0; j < l.get(i).size(); j++) {
						setCellValue(String.class, l.get(i).get(j), createCell());
					}
				}
			}
		}
		return this;
	}

	private int countMapList(Map<String, List<List<Object>>> map) {
		int count = 0;
		Iterator<String> mapIt = map.keySet().iterator();
		while (mapIt.hasNext()) {
			count += map.get(mapIt.next()).size();
		}
		return count;
	}

	/**
	 * 创建新内容
	 * 
	 * @param clazz
	 *            单元格内容的类型
	 * @param value
	 *            单元格内容的数据
	 * @return
	 */
	public CreateExcel setCellValue(Class<?> clazz, Object value, Cell c) {
		c.setCellStyle(this.style);
		if (null == value) {
			c.setCellValue("");
			return this;
		}
		// 根据数据的类型set值
		if (clazz == java.lang.String.class) {
			c.setCellValue(value.toString());
		} else if (clazz == java.lang.Integer.class || clazz == int.class || clazz == java.lang.Double.class
				|| clazz == double.class || clazz == java.lang.Long.class || clazz == long.class
				|| clazz == java.lang.Short.class || clazz == short.class || clazz == java.lang.Float.class
				|| clazz == float.class) {
			c.setCellValue(Double.parseDouble(value.toString()));
		} else if (clazz == java.lang.Boolean.class || clazz == boolean.class) {
			c.setCellValue(Boolean.parseBoolean(value.toString()));
		} else if (clazz == java.util.Date.class) {
			CreationHelper createHelper = wb.getCreationHelper();

			// 日期处理
			if (value.toString().length() >= 8 && value.toString().length() <= 10) {
				this.style2.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
			} else {
				this.style2.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss"));
			}
			c.setCellValue((Date) value);
			c.setCellStyle(this.style2);
		} else {
			throw new RuntimeException("setCellValue:未知的参数类型！");
		}
		// c.setCellStyle(this.style);
		// initStyle();
		return this;
	}

	/**
	 * 创建新的单元格(合并单元格)
	 * 
	 * @param rowSpan
	 *            跨行数
	 * @param colSpan
	 *            跨列数
	 * @return
	 */
	public Cell createCell(int rowSpan, int colSpan) {
		Cell c = curRow.createCell(curColIndex);
		CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex + rowSpan - 1, curColIndex,
				curColIndex + colSpan - 1);
		sheet.addMergedRegion(cellRangeAddress);
		spanStyle(cellRangeAddress);
		
		curColIndex += colSpan;
		c.setCellStyle(style);
		return c;
	}
	
	/**
	 * 设置合并单元格样式
	 * @param cellRangeAddress
	 */
	private void spanStyle(CellRangeAddress cellRangeAddress) {
		RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, wb); 	// 左边框
		RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, wb); 	// 上边框
		RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, wb); 	// 右边框
	}

	/**
	 * 创建新的单元格
	 * 
	 * @return
	 */
	public Cell createCell() {
		Cell c = curRow.createCell(curColIndex);
		curColIndex++;
		return c;
	}

	/**
	 * 得到实体的带有注解的字段的内容
	 * 
	 * @param class1
	 * @return
	 */
	private Map<String, ColProperty> findBeanProperty(Class<?> clazz) {
		Field fs[] = clazz.getDeclaredFields();
		Map<String, ColProperty> indexMap = new HashMap<String, ColProperty>();
		for (Field f : fs) {
			// 寻找有注解的字段
			TabCol tabCol = f.getAnnotation(TabCol.class);
			if (null == tabCol) {
				continue;
			} else {
				ColProperty colProperty = new ColProperty(f.getType(), tabCol.Index(), tabCol.title(),tabCol.width());
				indexMap.put(f.getName(), colProperty);
			}
		}

		return indexMap;
	}
	
	private Map<String, ColProperty> findBeanProperty(Class<?> clazz, List<Class> listClazz) {
		Field fs[] = clazz.getDeclaredFields();
		Map<String, ColProperty> indexMap = new HashMap<String, ColProperty>();
		Integer n = 0, i;
		for (Field f : fs) {
			// 寻找有注解的字段
			TabCol tabCol = f.getAnnotation(TabCol.class);
			if (null == tabCol) {
				continue;
			} else {
				// 判断是否包含其中一个注解
				Boolean isHas = false;
				for (Class cz : listClazz) {
					if (f.getAnnotation(cz) != null) {
						isHas = true;
						break;
					}
				}
				if (!listClazz.isEmpty() && listClazz.size() > 0 && !isHas) {
					n++;
				}
				// 注解为空时直接获取，不为空时需加判断
				if ((listClazz.isEmpty() || 0 == listClazz.size()) || (!listClazz.isEmpty() && listClazz.size() > 0 && isHas)) {
					i = tabCol.Index();
					if (i > n) {
						// 重新设置导出列索引
						i -= n;
					}
					ColProperty colProperty = new ColProperty(f.getType(), i, tabCol.title(),tabCol.width());
					indexMap.put(f.getName(), colProperty);
				}
			}
		}

		return indexMap;
	}

	/**
	 * 得到表头的集合
	 * 
	 * @param map
	 * @return
	 */
	private List<String> getHead(Map<String, ColProperty> map) {
		List<String> heads = new ArrayList<String>();

		for (int i = 0; i < map.size(); i++) {
			Iterator<String> it = map.keySet().iterator();
			while (it.hasNext()) {
				String key = it.next();
				ColProperty value = map.get(key);
				if (value.getIndex() != (i + 1)) {
					continue;
				}
				heads.add(value.getTitle());
			}
		}
		return heads;
	}
	
	/**
	 * 得到表头数据
	 * @return
	 */
	private List<ColProperty> getHeadProperty(Map<String, ColProperty> map){
		List<ColProperty> heads = new ArrayList<ColProperty>();

		for (int i = 0; i < map.size(); i++) {
			Iterator<String> it = map.keySet().iterator();
			while (it.hasNext()) {
				String key = it.next();
				ColProperty value = map.get(key);
				if (value.getIndex() != (i + 1)) {
					continue;
				}
				heads.add(value);
			}
		}
		return heads;
	}

	/**
	 * 创建统计数据
	 * 
	 * @param t
	 *            包含统计数据的实体
	 * @return
	 */
	public CreateExcel createCounts(Object t) {
		createRow();
		Map<String, CountProperty> map = getCountProperty(t.getClass());
		Method m = null;
		for (int i = 0; i < map.size(); i++) {
			Iterator<String> it = map.keySet().iterator();
			while (it.hasNext()) {
				String key = it.next();
				// 查找注解字段
				CountProperty countProperty = map.get(key);
				if (countProperty.getIndex() != (i + 1)) {
					continue;
				}
				try {
					m = t.getClass().getMethod("get" + toUpperCaseFirstOne(key), null);
					Class<?> clazz = countProperty.getType();
					Object oo;
					// 反射得到值
					oo = m.invoke(t, null);
					if (countProperty.getColSpan() == 1) {
						setCellValue(clazz, oo, createCell());
					} else {
						// 根据注解判断是否合并单元格
						if (countProperty.getIsCoalition()) {
							setCellValue(clazz, oo, createCell(0, countProperty.getColSpan()));
						} else {
							setCellValue(clazz, oo, createCell());
							// 填充空的单元格
							for (int j = 0; j < countProperty.getColSpan() - 1; j++) {
								setCellValue(String.class, "", createCell());
							}
						}
					}
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				}

			}
		}

		return this;
	}

	/**
	 * 处理统计数据的属性，得到值
	 * 
	 * @param clazz
	 * @return
	 */
	public Map<String, CountProperty> getCountProperty(Class<?> clazz) {
		Field fs[] = clazz.getDeclaredFields();
		Map<String, CountProperty> indexMap = new HashMap<String, CountProperty>();
		for (Field f : fs) {
			// 寻找有注解的字段
			CountData countData = f.getAnnotation(CountData.class);
			if (null == countData) {
				continue;
			} else {
				CountProperty countProperty = new CountProperty(f.getType(), countData.index(), countData.colSpan(),
						countData.isCoalition());
				indexMap.put(f.getName(), countProperty);
			}
		}
		return indexMap;
	}

	/**
	 * 生成Excel文件
	 * 
	 * @param filePath
	 */
	public void writeToFile(String filePath) {
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(new File(filePath));
			wb.write(fos);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 返回workbook对象
	 * 
	 * @return
	 */
	public Workbook getWorkbook() {
		return wb;
	}

	/**
	 * 生成带有统计数据的excel
	 * 
	 * @param title
	 *            表头数据
	 * @param body
	 *            表的内容-->带有注解的泛型实体
	 * @param t
	 *            统计数据
	 * @return
	 */
	public CreateExcel CreateNewExcelNoTemplate(String title, List<?> body, Object t) {
		if (null == title || "".equals(title)) {
			throw new RuntimeException("表头不能为空!");
		}
		if (null == body || body.size() == 0) {
			throw new RuntimeException("表内容为空!");
		}
		if (null == t) {
			throw new RuntimeException("统计数据为空!");
		}
		initDataNoTemplate();
		initConfigDataNoTemplate();
		List<String> heads = getHead(findBeanProperty(body.get(0).getClass()));
		createTitle(title, heads.size() - 1);
		createHead(heads);
		createBody(body);
		createCounts(t);
		return this;
	};

	public CreateExcel CreateNewExcelNoTemplate(String title, List<?> body) {
		initDataNoTemplate();
		if (null == title || "".equals(title)) {
//			throw new RuntimeException("表头不能为空!");
			return this;
		}
		if (null == body || body.size() == 0) {
			return this;
//			throw new RuntimeException("表内容为空!");
		}
		initConfigDataNoTemplate();
		List<ColProperty> heads = getHeadProperty(findBeanProperty(body.get(0).getClass()));
		createTitle(title, heads.size() - 1);

		createHeadProperty(heads);
		createBody(body);
		return this;
	};
	
	/**
	 * 创建新的excel
	 * @param title 表头数据
	 * @param body 表的内容-->带有注解的泛型实体
	 * @param listClazz 只导出带有这些注解的数据
	 * @return CreateExcel
	 */
	public CreateExcel CreateNewExcelNoTemplate(String title, List<?> body, List<Class> listClazz) {
		initDataNoTemplate();
		if (null == title || "".equals(title)) {
//			throw new RuntimeException("表头不能为空!");
			return this;
		}
		if (null == body || body.size() == 0) {
			return this;
//			throw new RuntimeException("表内容为空!");
		}
		initConfigDataNoTemplate();
		List<ColProperty> heads = getHeadProperty(findBeanProperty(body.get(0).getClass(), listClazz));
		createTitle(title, heads.size() - 1);

		createHeadProperty(heads);
		createBody(body, listClazz);
		return this;
	};
	
	

	public CreateExcel CreateNewExcelWithTemplate(String title, List<?> body, Object o, String filePath) {
		if (null == title || "".equals(title)) {
			throw new RuntimeException("表头不能为空!");
		}
		if (null == body || body.size() == 0) {
			throw new RuntimeException("表内容为空!");
		}
		if (null == o) {
			throw new RuntimeException("统计数据为空!");
		}
		readTemplate(filePath);
		initConfigData();
		List<String> heads = getHead(findBeanProperty(body.get(0).getClass()));
		createTitle(title, heads.size() - 1);
		createHead(heads);
		createBody(body);
		createCounts(o);
		return this;
	}

	public CreateExcel CreateNewExcelWithTemplate(String title, List<?> body, String filePath) {
		if (null == title || "".equals(title)) {
			throw new RuntimeException("表头不能为空!");
		}
		if (null == body || body.size() == 0) {
			throw new RuntimeException("表内容为空!");
		}
		readTemplate(filePath);
		initConfigData();
		List<String> heads = getHead(findBeanProperty(body.get(0).getClass()));
		createTitle(title, heads.size() - 1);
		createHead(heads);
		createBody(body);
		CreateNewExcelNoTemplate(title, body);
		return this;
	}

	/**
	 * 读取表头模板
	 * 
	 * @param filePath
	 * @return
	 */
	public CreateExcel readTemplate(String filePath) {
		try {
			wb = new HSSFWorkbook(new FileInputStream(new File(filePath)));
			sheet = wb.getSheetAt(0);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("未找到相关模板！");
		} catch (IOException e) {
			e.printStackTrace();
		}
		return this;
	}

	/**
	 * 查找数据的起始标记
	 */
	@SuppressWarnings("static-access")
	private void initConfigData() {
		boolean findData = false;
		for (Row row : sheet) {
			if (findData)
				break;
			for (Cell cell : row) {
				if (cell.getCellType() != cell.CELL_TYPE_STRING)
					continue;
				String str = cell.getStringCellValue();
				// DATA_START就是默认的起始标记
				if (str.equals(DATA_START)) {
					initColIndex = cell.getColumnIndex();
					initRowIndex = row.getRowNum();
					// 初始化数据
					curColIndex = initColIndex;
					curRowIndex = initRowIndex;
					findData = true;
					break;
				}
			}
		}
	}

	/**
	 * 将第一个字母转换为大写
	 * @param s
	 * @return
	 */
	public static String toUpperCaseFirstOne(String s) {
		if (Character.isUpperCase(s.charAt(0))) {
			return s;
		} else {
			return (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();
		}
	}
}
